<%--
  Created by IntelliJ IDEA.
  User: mafh
  Date: 2017/6/12 0012
  Time: 16:15
  To change this template use File | Settings | File Templates.
--%>
<%@ page pageEncoding="UTF-8" %>
<%!
    /**
     * 检查该月数据是否已存过
     * @param input
     * @return
     */
    public Message salaryCheck(Input input){
        Message msg = new Message();
        Integer hospital = input.getInt("hospital",-999);
        String date = input.getDesc("date","");
        List<String> dataList = QBuilder.db("basedb").select("count(*)").from("SalaryModule").where(new Where("hospital=",hospital).and("date=",date)).query().getResultObj();
        msg.put("data",dataList.get(0));//数目为0表示没有存储过数据，不为0则表示存储过数据，需要提示是否确认覆盖
        return msg;
    }

    /**
     * 导入工资条之前的确认工作
     * @param input
     * @return
     */
    public Message salaryExcelInit(Input input) throws Exception {
        Message msg = new Message();
        String urlFileName = input.getDesc("urlFileName","");
        String fileName = urlFileName.substring(urlFileName.lastIndexOf("/")+1);
        urlFileName = urlFileName.substring(0,urlFileName.lastIndexOf("/"))+"/"+java.net.URLEncoder.encode(fileName,"UTF-8");
        jxl.Workbook workbook;
        InputStream is = null;
        try {
            URL url = new URL(urlFileName);
            URLConnection urlConnection = url.openConnection();
            urlConnection.connect();
            HttpURLConnection httpURLConnection = (HttpURLConnection)urlConnection;
            if(httpURLConnection.getResponseCode() != HttpURLConnection.HTTP_OK){
                msg.setErrcodeAndErrmsg(23333,"URL地址解析异常");
            }else {
                //解析正确，读取文件流
                is = httpURLConnection.getInputStream();
                workbook = jxl.Workbook.getWorkbook(is);
                jxl.Sheet sheet = workbook.getSheet(0);//获取第一张sheet表
                int columns = sheet.getColumns();//获取改动列数
                List<String> addList = new ArrayList<>();//存放记录
                if(columns > 3){
                    for(int c=3;c<columns;c++){//循环列数，获取标题
                        addList.add(sheet.getCell(c,0).getContents());
                    }
                    msg.put("data",addList);
                }else{
                    msg.setErrcodeAndErrmsg(23333,"excel格式异常");
                }
            }
        }catch (Exception e){
            msg.setErrcodeAndErrmsg(23333,"excel异常");
            e.printStackTrace();
        }finally {
            if(is != null){//关闭文件流
                is.close();
            }
        }
        return msg;
    }

    /**
     * 导入工资条的excel数据
     * @param input
     * @return
     */
    public Message salaryExcel(Input input) throws Exception {
        Message msg = new Message();
        String urlFileName = input.getDesc("urlFileName","");
        String fileName = urlFileName.substring(urlFileName.lastIndexOf("/") + 1);
        urlFileName = urlFileName.substring(0,urlFileName .lastIndexOf("/"))+"/"+java.net.URLEncoder.encode(fileName,"utf-8");
        Integer hospital = input.getInt("hospital",-999);
        String names = input.getDesc("names","");//合计名称字符串
        String indexs = input.getDesc("indexs","");//匹配字符串
        String date = input.getDesc("date","");//薪资日期
        jxl.Workbook workbook;
        InputStream is = null;
        Integer errNum = 0;
        try {
            URL url = new URL(urlFileName);//获取excel地址对象
            URLConnection urlConnection = url.openConnection();
            urlConnection.connect();
            HttpURLConnection httpURLConnection = (HttpURLConnection)urlConnection;
            if(httpURLConnection.getResponseCode() != HttpURLConnection.HTTP_OK){
                msg.setErrcodeAndErrmsg(23333,"URL地址解析异常");
                return msg;
            }else {
                //解析正确，读取文件流
                is = httpURLConnection.getInputStream();
                workbook = jxl.Workbook.getWorkbook(is);
                jxl.Sheet sheet = workbook.getSheet(0);//获取第一张sheet表
                int rows = sheet.getRows();//获取改动行数
                int columns = sheet.getColumns();//获取改动列数
                List<SalaryModule> addList = new ArrayList<>();//存放记录
                String[] indexArray = indexs.split(",");
                String[] nameArray = names.split(",");
                for(int r=1;r<rows;r++){//循环获取数据记录
                    errNum = r;
                    String employeeId = sheet.getCell(0,r).getContents();//员工编号
                    if(employeeId.equals("")){//判断空值
                        msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行员工编号数据异常");
                        return msg;
                    }
                    String employeeName = sheet.getCell(1,r).getContents();//员工姓名
                    if(employeeName.equals("")){//判断空值
                        msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行员工姓名数据异常");
                        return msg;
                    }
                    String phone = sheet.getCell(2,r).getContents();//手机号
                    if(phone.equals("")||!ValidateUtil.validatePhone(phone)){//判断空值
                        msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行手机号数据异常");
                        return msg;
                    }
                    List<Map> columnList = new ArrayList<>();
                    List<String> titleList = new ArrayList<>();
                    Map moduleMap = new HashMap();
                    for(int c=3;c<columns;c++){//循环列数
                        Map columnMap = new HashMap();
                        String titleKey = sheet.getCell(c,0).getContents();
                        String titleValue = sheet.getCell(c,r).getContents();
                        if(titleValue.equals("")||!Tools.isNumeric(titleValue)){//判断空值
                            msg.setErrcodeAndErrmsg(23333,"第"+(r+1)+"行第"+(c+1)+"列数据异常，数值不能为空或非数字");
                            return msg;
                        }
                        columnMap.put("name",titleKey);
                        columnMap.put("value",titleValue);
                        columnMap.put("level","0");
                        titleList.add(titleKey);
                        columnList.add(columnMap);
                    }
                    for(int a=0;a<indexArray.length;a++){//循环配置数组
                        Map columnMap = columnList.get(a+3);
                        columnMap.put("name",nameArray[a]);
                        columnMap.put("level","1");
                    }
                    moduleMap.put("data",columnList);
                    moduleMap.put("titles",titleList);
                    SalaryModule salaryModule = new SalaryModule();
                    salaryModule.setEmployeeId(employeeId);
                    salaryModule.setName(employeeName);
                    salaryModule.setPhone(phone);
                    salaryModule.setHospital(hospital);
                    salaryModule.setDate(date);
                    salaryModule.setJsonString(JSONUtil.toJson(moduleMap));
                    addList.add(salaryModule);
                }
                //删除原有数据
                Integer num = QBuilder.db("basedb").execHQL("delete from SalaryModule where date ='"+date+"' and hospital="+hospital);
                if(num > -1){//删除成功
                    List<Integer> keyList =  DBTools.db("basedb").addItem(addList);
                    if(keyList.isEmpty()){//判空，失败
                        msg.setErrcodeAndErrmsg(23333,"数据存储异常");
                    }
                }else{//删除失败
                    msg.setErrcodeAndErrmsg(23333,"数据删除异常，请在刷新后重试");
                }
            }
        }catch (Exception e){
            msg.setErrcodeAndErrmsg(23333, "第" + (errNum + 1) + "行数据异常");
            e.printStackTrace();
        }finally{
            if(is != null){//关闭文件流
                is.close();
            }
        }
        return msg;//返回结果
    }
%>
